#include "database.h"


Databass::Databass()
{
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("attendance.db");
    openDb();
    createTable();
}

Databass::~Databass()
{
    db.close();
}


void Databass::createTable()
{
    /**
    *此函数用于创建数据库表单
    * 1、创建考勤记录表：根据获取的本地时间，以Attendance_xxxx_xx_xx为名称,默认存在两个字段：序号 员工工号
    * 2、创建员工信息表：名称：employees,默认存在两个字段：employeeId,name
    * 当存在此表单，就不创建，不存在就创建；
    */

    //1、创建数据库对象
    QSqlQuery query;
    //2、创建Attendance_xxxx/xx/xx考勤记录表
    QString localTime = getTime();
    tableName = "Attendance_"+localTime;
    QString creatTable_sql = "create table if not exists " +
            tableName +
            " (serialNum integer primary key autoincrement,"
            "employeeId)";
    if(!query.exec(creatTable_sql))
    {
        qDebug()<<query.lastError().databaseText();
        return ;
    }
    //3、创建employees表格,记录员工信息

    creatTable_sql = "create table if not exists employees (employeeId varchar(20) primary key,name varchar(20))";

    if(!query.exec(creatTable_sql))
    {
        qDebug()<<query.lastError().databaseText();
        return ;
    }
}


int Databass::punch(std::string id, time_t time)
{

    /**
     * 添加打卡记录
     * @param id 员工工号
     * @param time 打卡时间，Epoch纪元时间，从1970/1/1到现在的秒数
     * @retval 0 打卡成功
     */
    QSqlQuery query;
    //1、数据处理,将id、time转为QString类型
        //id处理
    QString emplyeeId = QString::fromStdString(id);
        //time处理
    char time_char[64];
    strftime(time_char, sizeof(time_char), "%Y/%m/%d %X %A",localtime(&time) );
    std::string time_string = time_char;
    QString clockTime = QString::fromStdString(time_string);

    //2、查询id
    QString selectId_sql = "select * from " + tableName + " where employeeId=" + "\'"+emplyeeId+ "\'";
    qDebug()<<"selectId_sql"<<selectId_sql;
    if(!query.exec(selectId_sql))
    {
        qDebug()<<query.lastError().databaseText();
        return -1;
    }

    //3、判断查询结果
    query.first();//将指针移动至查询结果的第一条数据

    //如果此id未存在，则创建此id
    if(query.value(0).toString() == "")
    {
        //插入emplyeeId
        QString insert_sql = "insert into " + tableName +"(employeeId) " + "values(" + "\'" + emplyeeId + "\'"+")";
        if(!query.exec(insert_sql))
        {
            qDebug()<<"addcolumn"<<query.lastError().databaseText();
            return -1;
        }
        //判断表中字段数量
        int ncolumn = getTableColoum();
        //若表中已创建多个字段，则无须再创建字段，直接更新打卡时间
        for(int i = 2; i < ncolumn; i++)
        {
            if(query.value(i).toString() == "")
            {
                QString num = QString::number(i-1);
                QString update_sql = "update " + tableName + " set "+"clockTime" + num + "=" + "\'"+clockTime +"\'" + " where employeeId="+"\'"+emplyeeId+"\'";
                qDebug()<<update_sql;
                if(!query.exec(update_sql))
                {
                    qDebug()<<query.lastError().databaseText();
                    return -1;
                }
                return 0;
            }
        }
        //for循环结束后，函数未结束，则表示表中还未创建字段，在此创建字段后，更新打卡时间

        QString addColumn_sql = "alter table " + tableName + " add column clockTime1";
        if(!query.exec(addColumn_sql))
        {
            qDebug()<<"addcolumn"<<query.lastError().databaseText();
            return -1;
        }

        QString update_sql = "update " + tableName + " set "+"clockTime1=" + "\'"+clockTime +"\'" +  " where employeeId="+"\'"+emplyeeId+"\'";
        qDebug()<<update_sql;
        if(!query.exec(update_sql))
        {
            qDebug()<<query.lastError().databaseText();
            return -1;
        }
    }
    //若此id已存在，根据字段数量，判断是否重新创建字段更新打卡时间，或是直接更新打卡时间
    else
    {
        //获取所有的列数
        int ncolumn = getTableColoum();
        qDebug()<<ncolumn;
        //for循环查找未写入的时间列，将记录插入到此字段后
        int i;
        for(i = 3; i < ncolumn; i++)//当查询到此id有信息后，此id所在行最少存在3列数据
        {
            if(query.value(i).toString() == "")
            {
                QString num = QString::number(i-1);//num用来记录clockTime字段的下标，
                QString update_sql = "update " + tableName + " set "+"clockTime" + num + "=" + "\'"+clockTime +"\'" +  " where employeeId="+"\'"+emplyeeId+"\'";
                qDebug()<<update_sql;
                if(!query.exec(update_sql))
                {
                    qDebug()<<query.lastError().databaseText();
                    return -1;
                }
                return 0;
            }
        }
        QString num = QString::number(i-1);
        QString addColumn_sql = "alter table " + tableName + " add column clockTime" + num;
        qDebug()<<addColumn_sql;
        if(!query.exec(addColumn_sql))
        {
            qDebug()<<query.lastError().databaseText();
            return -1;
        }
        QString update_sql = "update " + tableName + " set "+"clockTime" + num + "=" + "\'"+clockTime +"\'" + " where employeeId="+"\'"+emplyeeId+"\'";
        qDebug()<<update_sql;
        if(!query.exec(update_sql))
        {
            qDebug()<<query.lastError().databaseText();
            return -1;
        }
        return 0;
    }

}
int Databass::getTableColoum()
{
    QSqlQuery query;
    QString select_sql = "select * from " + tableName;
    query.exec(select_sql);
    QSqlQueryModel *queryModel = new QSqlQueryModel();
    queryModel->setQuery(query);
    int ncolumn = queryModel->columnCount();
    return ncolumn;
}

int Databass::insertEmployeeInfo(std::string id, std::string name)
{
    /*
     * 向employees表中插入员工信息
     * @param id 员工工号
     * @param name 员工姓名
     * @retval 0 插入成功
     * @retval -1 插入失败
    */
    QSqlQuery query;
    QString employeeId = QString::fromStdString(id);
    QString employeeName = QString::fromStdString(name);

    query.prepare("insert into employees (employeeId,name) values(?,?)");
    query.addBindValue(employeeId);
    query.addBindValue(employeeName);
    if(!query.exec())
    {
        qDebug()<<query.lastError().databaseText();
        return -1;
    }
    return 0;
}

int Databass::getname(std::string id,std::string & name)
{
    /*
     * 通过id在employee表中查询name
     * @param id 员工工号
     * @param name 将查询到的姓名填入name中
     * @retval 0 查询成功
     * @retval -1 未查询到
    */

    QSqlQuery query;
    QString employeeId = QString::fromStdString(id);

    QString select_sql = "select * from employees where employeeId = " + employeeId;

    if(!query.exec(select_sql))
    {
        qDebug()<<query.lastError().databaseText();
        return -1;
    }

    while(query.next())
    {
        QString s = query.value(1).toString();
        qDebug()<<"name"<<s;

        name = s.toStdString();
        return 0;
    }
    return -1;
}

void Databass::openDb()
{
    /*
     * 打开数据库
     */
    bool ok = db.open();
    if(!ok)
    {
        //打印最近函数库调用失败的原因
        qDebug()<<db.lastError().text()<<endl;
    }
}

QString Databass::getTime()
{
    /*
     * 获取本地时间
     * @return 返回获取到的本地时间xxxx_xx_xx
     *
    */
    QDateTime current_date_time =QDateTime::currentDateTime();
    QString current_date =current_date_time.toString("yyyy.MM.dd");
    current_date = current_date.replace(QRegExp("\\."), "_");
    return  current_date;
}
